using System.Threading.Tasks;
using GetKlient.Models;
using Microsoft.EntityFrameworkCore;

namespace GetKlient.Services
{
    public class OpenInsDetailService
    {
        private readonly PFlibDbContext _dbContext;

        public OpenInsDetailService(PFlibDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        public async Task<Models.OpenData[]> SelectOpenData()
        {
            try
            {
                var query = from oid in _dbContext.OpenInsDetail
                            join m in _dbContext.Monitor on oid.DevicesOpenID equals m.SCode
                            select new Models.OpenData
                            {
                                Openid = oid.OpenID,
                                PassagewayId = oid.PassagewayID,
                                DevicesOpenid = oid.DevicesOpenID
                            };
                return await query.ToArrayAsync();
            }
            catch (System.Exception ex)
            {
                throw new System.Exception($"查询 OpenData 时出错：{ex.Message}");
            }
        }

        public async Task InsertOpenInsDetail(string openid, string title, string passagewayId, string devicesOpenid)
        {
            try
            {
                var openInsDetail = new Models.OpenInsDetail
                {
                    OpenID = openid,
                    Title = title,
                    PassagewayID = passagewayId,
                    DevicesOpenID = devicesOpenid
                };
                _dbContext.OpenInsDetail.Add(openInsDetail);
                await _dbContext.SaveChangesAsync();
            }
            catch (System.Exception ex)
            {
                throw new System.Exception($"插入 OpenInsDetail 时出错：{ex.Message}");
            }
        }

        public async Task<bool> TableExists()
        {
            try
            {
                var result = await _dbContext.Database.ExecuteSqlRawAsync("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'OpenInsDetail'");
                return result > 0;
            }
            catch (System.Exception ex)
            {
                throw new System.Exception($"判断 OpenInsDetail 表是否存在时出错：{ex.Message}");
            }
        }

        public async Task CreateTable()
        {
            try
            {
                await _dbContext.Database.ExecuteSqlRawAsync("CREATE TABLE OpenInsDetail (id INT IDENTITY(1,1) PRIMARY KEY, openid VARCHAR(255), title VARCHAR(255), passagewayId VARCHAR(255), devicesOpenid VARCHAR(255))");
            }
            catch (System.Exception ex)
            {
                throw new System.Exception($"创建 OpenInsDetail 表时出错：{ex.Message}");
            }
        }
    }
}